package easybooking.db;

import easybooking.data.Customer;
import easybooking.data.Flight;
import easybooking.data.Booking;
import easybooking.data.BookingImpl;
import java.rmi.RemoteException;
import java.text.ParseException;
import java.util.Vector;
import java.sql.*;

/**
 * Mapper-Class, which displays <code>Booking</code> objects in a relational
 * database. Therefore a collection of methods to search, create and modify
 * will be provided.
 * The mapping is bidirectional, which means, that objects could be transformed
 * into database-structures and database-structures into objects.
 *
 * @author Mark Roman Stehle
 */
// <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
// #[regen=yes,id=DCE.2FE9941B-FEE5-74AE-1D8C-F9B9AA83B072]
// </editor-fold> 
public class BookingMapper extends Mapper{
    /**
     * The class <code>BookingMapper</code> will be instantiated only once.
     * This is called <b>Singleton</b>, because of the <code>static</code>-tag,
     * the variable exists only once for all instances of this class. The
     * variable saves the only instance of this class.
     *
     * @see bookingMapper()
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.EC93DCED-023D-9D88-F284-ADE9954BA1F8]
    // </editor-fold> 
    private static BookingMapper bookingMapper = null;

    /**
     * The protected constructor prevents the possibility to generate a new
     * instance of this class.
     */
    protected BookingMapper() {
    }

    /**
     * The static method can be called with
     * <code>BookingMapper.bookingMapper()</code>.
     * This guarantees the singleton property by checking so that only one
     * instance of the <code>BookingMapper</code> exists.

     * <b>Conclusion:</b> The <code>BookingMapper</code> should not be
     * instantiated with <code>new</code>, but rather by calling the static
     * method.

     * @return The <code>BookingMapper</code> object.
     * @see bookingMapper
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.E6700B24-4A8B-1E4D-35A4-CB5038480114]
    // </editor-fold> 
    public static BookingMapper bookingMapper () {
        if (bookingMapper == null) {
            bookingMapper = new BookingMapper();
        }
        return bookingMapper;
    }

    /**
     * Searching a booking with given key, since the key is distinct only one
     * object will be returned.
     *
     * @param id primary key (->DB)
     * @return Booking object, which corresponds the given key,
     * <code>null</code> for a non existing database tupel.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.C744B89B-0D36-CC7B-8477-90970A1EF649]
    // </editor-fold> 
    public Booking findByKey (int id) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                "SELECT bookingId, dateTime, isCheckedIn, flightId, seatId, customerId, checkedInDateTime, isActive FROM Booking " +
                "WHERE bookingId=" + id);
            if (rs.next()) {
                try {
                    Booking b = new BookingImpl();
                    b.setBookingId(rs.getInt("bookingId"));
                    b.setDateTime(sqlDateToUtilDate(rs.getTimestamp("dateTime")));
                    b.setIsCheckedIn(rs.getBoolean("isCheckedIn"));
                    b.setFlightId(rs.getInt("flightId"));
                    b.setSeatId(rs.getInt("seatId"));
                    b.setCustomerId(rs.getInt("customerId"));
                    b.setCheckedInDateTime(sqlDateToUtilDate(rs.getTimestamp("checkedInDateTime")));
                    b.setIsActive(rs.getBoolean("isActive"));
                    return b;
                } catch (Exception ex) {
                    ex.printStackTrace();
                    return null;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
            return null;
        }
        return null;
    }

    /**
     * Selecting all active bookings from the database.
     *
     * @return A vector of booking objects, which includes all active bookings.
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.4449C9D2-2864-0F78-017B-FB129BB0F63E]
    // </editor-fold> 
    public Vector<Booking> findAll() {
        Connection con = DBConnection.connection();
        Vector<Booking> result = new Vector<Booking>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                "SELECT bookingId, dateTime, isCheckedIn, flightId, seatId, customerId, checkedInDateTime, isActive FROM Booking " +
                "WHERE isActive = true " +
                "ORDER BY bookingId ASC");
            while (rs.next()) {
                try {
                    Booking b = new BookingImpl();
                    b.setBookingId(rs.getInt("bookingId"));
                    b.setDateTime(sqlDateToUtilDate(rs.getTimestamp("dateTime")));
                    b.setIsCheckedIn(rs.getBoolean("isCheckedIn"));
                    b.setFlightId(rs.getInt("flightId"));
                    b.setSeatId(rs.getInt("seatId"));
                    b.setCustomerId(rs.getInt("customerId"));
                    b.setCheckedInDateTime(sqlDateToUtilDate(rs.getTimestamp("checkedInDateTime")));
                    b.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(b);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }

    /**
     * Inserting a <code>Booking</code> object into the database. Before that,
     * the primary key of the given object will be verified und corrected.
     *
     * @param b the object to be saved
     * @return The given object, with corrected primary key.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.CBFF0734-DFA4-6CFF-EBEB-CB94225EB566]
    // </editor-fold> 
    public Booking insert (Booking b) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                "SELECT MAX(bookingId) AS maxBookingId " +
                "FROM Booking");
            if (rs.next()) {
                try {
                    b.setBookingId(rs.getInt("maxBookingId") + 1);
                    stmt = con.createStatement();
                    stmt.executeUpdate("INSERT INTO Booking (bookingId, dateTime, isCheckedIn, flightId, seatId, customerId, checkedInDateTime, isActive) " +
                            "VALUES (" +
                            b.getBookingId() + ",'" +
                            utilDateToSqlDate(b.getDateTime()) + "','" +
                            booleanToInt(b.getIsCheckedIn()) + "','" +
                            b.getFlightId() + "','" +
                            b.getSeatId() + "','" +
                            b.getCustomerId() + "','" +
                            utilDateToSqlDate(b.getCheckedInDateTime()) + "','" +
                            booleanToInt(b.getIsActive()) + "')");
                }
                catch (RemoteException ex) {
                    ex.printStackTrace();
                }
                catch (ParseException pe) {
                    pe.printStackTrace();
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return b;
    }

    /**
     * Rewriting an object into the database.
     *
     * @param b the object which sould be written into the database
     * @return The given object.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.5FDAA524-FACE-6A0E-708B-EB44BE2EBC90]
    // </editor-fold> 
    public Booking update (Booking b) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            stmt.executeUpdate("UPDATE Booking " +
            "SET dateTime=\"" + utilDateToSqlDate(b.getDateTime()) + "\", " +
            "isCheckedIn=\"" + booleanToInt(b.getIsCheckedIn()) + "\", " +
            "flightId=\"" + b.getFlightId() + "\", " +
            "seatID=\"" + b.getSeatId() + "\", " +
            "customerId=\"" + b.getCustomerId() + "\", " +
            "checkedInDateTime=\"" + utilDateToSqlDate(b.getCheckedInDateTime()) + "\", " +
            "isActive=\"" + booleanToInt(b.getIsActive()) + "\" " +
            "WHERE bookingId=" + b.getBookingId());
        } catch (SQLException e2) {
            e2.printStackTrace();
        } catch (RemoteException e) {
            e.printStackTrace();
        }
        catch (ParseException pe) {
            pe.printStackTrace();
        }
        return b;
    }

    /**
     * Selecting all active airports from the database with a certain date,
     * which is given through a parameter.
     *
     * @param date the date which should be searched for
     * @return A vector of booking objects, which includes all active bookings
     * with a certain date.
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.1CBEC6CA-BECA-93B4-A365-E8CEC8E46440]
    // </editor-fold> 
    public Vector<Booking> findByDate (java.util.Date date) {
        Connection con = DBConnection.connection();
        Vector<Booking> result = new Vector<Booking>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                "SELECT bookingId, dateTime, isCheckedIn, flightId, seatId, customerId, checkedInDateTime, isActive FROM Booking " +
                "WHERE dateTime=" + utilDateToSqlDate(date) +
                " AND isActive = true " +
                " ORDER BY bookingId ASC");
            while (rs.next()) {
                try {
                    Booking b = new BookingImpl();
                    b.setBookingId(rs.getInt("bookingId"));
                    b.setDateTime(sqlDateToUtilDate(rs.getTimestamp("dateTime")));
                    b.setIsCheckedIn(rs.getBoolean("isCheckedIn"));
                    b.setFlightId(rs.getInt("flightId"));
                    b.setSeatId(rs.getInt("seatId"));
                    b.setCustomerId(rs.getInt("customerId"));
                    b.setCheckedInDateTime(sqlDateToUtilDate(rs.getTimestamp("checkedInDateTime")));
                    b.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(b);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        catch (ParseException pe) {
            pe.printStackTrace();
        }
        return result;
    }

    /**
     * Selecting all active bookings from the database from a certain customer,
     * which is given through a parameter.
     *
     * @param customerId the id of the customer from which the bookings
     * should be searched
     * @return A vector of booking objects, which includes all active bookings
     * from a certain customer.
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.200FE28E-A9BD-4FAC-8071-DCF92A86980B]
    // </editor-fold> 
    public Vector<Booking> findByCustomer (int customerId) {
        Connection con = DBConnection.connection();
        Vector<Booking> result = new Vector<Booking>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                "SELECT bookingId, dateTime, isCheckedIn, flightId, seatId, customerId, checkedInDateTime, isActive FROM Booking " +
                "WHERE customerId=" + customerId +
                " AND isActive = true " +
                "ORDER BY bookingId ASC");
            while (rs.next()) {
                try {
                    Booking b = new BookingImpl();
                    b.setBookingId(rs.getInt("bookingId"));
                    b.setDateTime(sqlDateToUtilDate(rs.getTimestamp("dateTime")));
                    b.setIsCheckedIn(rs.getBoolean("isCheckedIn"));
                    b.setFlightId(rs.getInt("flightId"));
                    b.setSeatId(rs.getInt("seatId"));
                    b.setCustomerId(rs.getInt("customerId"));
                    b.setCheckedInDateTime(sqlDateToUtilDate(rs.getTimestamp("checkedInDateTime")));
                    b.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(b);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }

    /**
     * Selecting all active bookings from the database from a certain customer,
     * which is given through a parameter.
     *
     * @param c the object of a customer from which the bookings
     * should be searched
     * @return A vector of booking objects, which includes all active bookings
     * from a certain customer. In case of an exception, an empty vector will
     * be returned.
     * @see findByCustomer(int customerId)
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker ">
    // #[regen=yes,id=DCE.A120F24D-C4DA-834D-8596-42619B343517]
    // </editor-fold>
    public Vector<Booking> findByCustomer (Customer c) {
        try {
            return BookingMapper.bookingMapper().findByCustomer(c.getCustomerId());
        } catch (RemoteException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * Selecting all active bookings from the database from a certain flight,
     * which is given through a parameter.
     *
     * @param flightId the id of the flight from which the bookings
     * should be searched
     * @return A vector of booking objects, which includes all active bookings
     * from a certain flight.
     * In case of an exception, an empty vector will be returned.
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker "> 
    // #[regen=yes,id=DCE.835F7027-1F41-E92D-A9E2-787C771C5A7B]
    // </editor-fold> 
    public Vector<Booking> findByFlight (int flightId) {
        Connection con = DBConnection.connection();
        Vector<Booking> result = new Vector<Booking>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                "SELECT bookingId, dateTime, isCheckedIn, flightId, seatId, customerId, checkedInDateTime, isActive FROM Booking " +
                "WHERE flightId=" + flightId +
                " AND isActive = true " +
                "ORDER BY bookingId ASC");
            while (rs.next()) {
                try {
                    Booking b = new BookingImpl();
                    b.setBookingId(rs.getInt("bookingId"));
                    b.setDateTime(sqlDateToUtilDate(rs.getTimestamp("dateTime")));
                    b.setIsCheckedIn(rs.getBoolean("isCheckedIn"));
                    b.setFlightId(rs.getInt("flightId"));
                    b.setSeatId(rs.getInt("seatId"));
                    b.setCustomerId(rs.getInt("customerId"));
                    b.setCheckedInDateTime(sqlDateToUtilDate(rs.getTimestamp("checkedInDateTime")));
                    b.setIsActive(rs.getBoolean("isActive"));
                    result.addElement(b);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    break;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
        }
        return result;
    }

    /**
     * Selecting all active bookings from the database from a certain flight,
     * which is given through a parameter.
     *
     * @param f the object of a flight from which the bookings
     * should be searched
     * @return A vector of booking objects, which includes all active bookings
     * from a certain flight. In case of an exception, an empty vector will
     * be returned.
     * @see findByFlight(int flightId)
     */
    // <editor-fold defaultstate="collapsed" desc=" UML Marker ">
    // #[regen=yes,id=DCE.478AF623-F319-DD76-6049-02B41FF18CF9]
    // </editor-fold>
    public Vector<Booking> findByFlight (Flight f) {
        try {
            return BookingMapper.bookingMapper().findByFlight(f.getFlightId());
        } catch (RemoteException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * Selecting all active bookings from the database from a certain flight,
     * and a certain seat which are given through parameters.
     *
     * @param flightId the id of the flight from which the bookings
     * should be searched
     * @param seatId the id of the seat from which the bookings should be
     * searched
     * @return A vector of booking objects, which includes all active bookings
     * from a certain flight with a certain seat.
     * In case of an exception, an empty vector will be returned.
     */
    public Booking findBookingByFlightAndSeat (int flightId, int seatId) {
        Connection con = DBConnection.connection();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(
                "SELECT bookingId, dateTime, isCheckedIn, flightId, seatId, customerId, checkedInDateTime, isActive FROM Booking" +
                " WHERE flightId=" + flightId +
                " AND seatId=" + seatId +
                " AND isActive = true");
            if (rs.next()) {
                try {
                    Booking b = new BookingImpl();
                    b.setBookingId(rs.getInt("bookingId"));
                    b.setDateTime(sqlDateToUtilDate(rs.getTimestamp("dateTime")));
                    b.setIsCheckedIn(rs.getBoolean("isCheckedIn"));
                    b.setFlightId(rs.getInt("flightId"));
                    b.setSeatId(rs.getInt("seatId"));
                    b.setCustomerId(rs.getInt("customerId"));
                    b.setCheckedInDateTime(sqlDateToUtilDate(rs.getTimestamp("checkedInDateTime")));
                    b.setIsActive(rs.getBoolean("isActive"));
                    return b;
                } catch (Exception ex) {
                    ex.printStackTrace();
                    return null;
                }
            }
        } catch (SQLException e2) {
            e2.printStackTrace();
            return null;
        }
        return null;
    }
}